/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package mpinventory;

import java.sql.*;
import java.util.List;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
/**
 *
 * @author user
 */
public class ItemQuery {
    private Connection conn;
    public Item item;
    private String searchBy;
    public ItemQuery(Connection conn){
        this.conn=conn;
    }
    public boolean canAddItem(String itemName, Float itemPrice){
        String query="select itemName FROM inventoryitemdata where itemName = ? and itemPrice = ?";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setString(1, itemName);
            prest.setFloat(2, itemPrice);
            ResultSet rs=prest.executeQuery();
            while(rs.next())
                return false;
        }
        catch(SQLException ex){
            Logger.getLogger(ItemQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }
    public void addItem(int itemID, float itemQuantity, String itemQuantityUnit, String itemName, float itemPrice){
        String query="insert into inventoryitemdata(itemID, itemQuantity, itemQuantityUnit, itemName, itemPrice) values(?,?,?,?,?)";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setInt(1, itemID);
            prest.setFloat(2, itemQuantity);
            prest.setString(3, itemQuantityUnit);
            prest.setString(4, itemName);
            prest.setFloat(5, itemPrice);
            prest.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully added " + itemName + " to the database!");
        }
        catch(SQLException ex){
            Logger.getLogger(ItemQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public List<Item> initializeItems(){
        List<Item> items=new ArrayList<Item>();
        try{
            Statement stmt=conn.createStatement();
            String query="select * from inventoryitemdata";
            ResultSet rs=stmt.executeQuery(query);
            while(rs.next()){
                Item item=new Item();
                item.setID(rs.getInt("itemID"));
                item.setQuantity(rs.getFloat("itemQuantity"));
                item.setQuantityUnit(rs.getString("itemQuantityUnit"));
                item.setDescription(rs.getString("itemName"));
                item.setUnitPrice(rs.getFloat("itemPrice"));
                items.add(item);
            }
        }
        catch(SQLException ex){
            Logger.getLogger(ItemQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return items;
    }
    public void editItem(int itemID, float itemQuantity, String itemQuantityUnit, String itemName, float itemPrice){
        String query="update inventoryitemdata set itemQuantity=?, itemQuantityUnit=?, itemName=?, itemPrice=? where itemID=?";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            prest.setFloat(1, itemQuantity);
            prest.setString(2, itemQuantityUnit);
            prest.setString(3, itemName);
            prest.setFloat(4, itemPrice);
            prest.setInt(5, itemID);
            prest.executeUpdate();
            JOptionPane.showMessageDialog(null, "Successfully edited " + itemName + "!");
        }
        catch(SQLException ex){
            Logger.getLogger(ItemQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    public void interpretSearchBy(int n){
        if(n==0)
            searchBy="All";
        else if(n==1)
            searchBy="Description";
        else if(n==2)
            searchBy="Quantity";
        else if(n==3)
            searchBy="Unit";
        else if(n==4)
            searchBy="UnitPrice";
    }
    public List<Item> search(String text){
        List<Item> items=new ArrayList<Item>();
        String query;
        if(searchBy.equals("Description"))
            query="select * FROM inventoryitemdata where itemName like ?";
        else if(searchBy.equals("Quantity"))
            query="select * FROM inventoryitemdata where itemQuantity >= ? AND itemQuantity <= ?";
        else if(searchBy.equals("Unit"))
            query="select * FROM inventoryitemdata where itemQuantityUnit like ?";
        else
            query="select * FROM inventoryitemdata where itemPrice >= ? AND itemPrice <= ?";
        try{
            PreparedStatement prest=conn.prepareStatement(query);
            if(searchBy.equals("Quantity")){
                float val=Float.parseFloat(text);
                prest.setFloat(1, val-5);
                prest.setFloat(2, val+5);
            }
            else if(searchBy.equals("UnitPrice")){
                float val=Float.parseFloat(text);
                prest.setFloat(1, val-20);
                prest.setFloat(2, val+20);
            }
            else
                prest.setString(1, "%"+text+"%");
            ResultSet rs=prest.executeQuery();
            while(rs.next()){
                Item item=new Item();
                item.setID(rs.getInt("itemID"));
                item.setQuantity(rs.getFloat("itemQuantity"));
                item.setQuantityUnit(rs.getString("itemQuantityUnit"));
                item.setDescription(rs.getString("itemName"));
                item.setUnitPrice(rs.getFloat("itemPrice"));
                items.add(item);
            }
        }
        catch(SQLException ex){
            Logger.getLogger(ItemQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        catch(NumberFormatException ex){
            Logger.getLogger(ItemQuery.class.getName()).log(Level.SEVERE, null, ex);
            if(!items.isEmpty())
                JOptionPane.showMessageDialog(null, "The keyword that you have just typed in does not belong to any item or falls under the wrong category. Please check your input.");
        }
        if(items.isEmpty()){
            JOptionPane.showMessageDialog(null, "The keyword that you have just typed in does not belong to any item or falls under the wrong category. Please check your input.");
            return null;
        }
        return items;
    }
    public int setID(){
        try{
            Statement stmt=conn.createStatement();
            String query="select max(itemID) as maxID from inventoryitemdata";
            ResultSet rs=stmt.executeQuery(query);
            while(rs.next())
                return rs.getInt("maxID")+1;
        }
        catch(SQLException ex){
            Logger.getLogger(ItemQuery.class.getName()).log(Level.SEVERE, null, ex);
        }
        return 0;
    }
}
